﻿CREATE TABLE [dbo].[AW_RecentRentSale] (
    [TranId]          UNIQUEIDENTIFIER CONSTRAINT [DF_AW_RecentRent_RentId] DEFAULT (newid()) NOT NULL,
    [LandType]        NVARCHAR (50)    NULL,
    [PropertyType]    NVARCHAR (50)    NULL,
    [Alamat]          NVARCHAR (500)   NULL,
    [Kawasan]         NVARCHAR (300)   NULL,
    [Project]         NVARCHAR (300)   NULL,
    [Daerah]          NCHAR (500)      NULL,
    [Price]           DECIMAL (18, 2)  NULL,
    [TransactionDate] DATETIME         NULL,
    [Land]            DECIMAL (18, 2)  NULL,
    [BuildUp]         DECIMAL (18, 2)  NULL,
    [Bedroom]         INT              CONSTRAINT [DF_AW_RecentRent_Bedroom] DEFAULT ((0)) NULL,
    [LSK]             TINYINT          CONSTRAINT [DF_AW_RecentRent_LSK] DEFAULT ((0)) NOT NULL,
    [TransactType]    NCHAR (4)        CONSTRAINT [DF_AW_RecentRentSale_TransactType] DEFAULT (N'RENT') NOT NULL,
    [CreateDate]      DATETIME         CONSTRAINT [DF_AW_RecentRent_CreateDate] DEFAULT (getdate()) NULL,
    [CreatedWho]      UNIQUEIDENTIFIER NULL,
    [UpdateDate]      DATETIME         NULL,
    [UpdateWho]       UNIQUEIDENTIFIER NULL,
    [StatusDelete]    TINYINT          CONSTRAINT [DF_AW_RecentRent_StatusDelete] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_AW_RecentRentSale] PRIMARY KEY CLUSTERED ([TranId] ASC)
);


GO
CREATE TRIGGER [dbo].[AW_RecentRentSale_dss_delete_trigger] ON [dbo].[AW_RecentRentSale] FOR DELETE AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 899026484 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_RecentRentSale_dss_tracking] AS [target] 
USING (SELECT [i].[TranId] FROM DELETED AS [i]) AS source([TranId]) 
ON ([target].[TranId] = [source].[TranId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 1, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[TranId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[TranId],@marker_create_scope_local_id, @marker_scope_create_peer_key, @marker_scope_create_peer_timestamp, 0, @marker_local_create_peer_timestamp , NULL, 0, 1, GETDATE() );
GO
CREATE TRIGGER [dbo].[AW_RecentRentSale_dss_update_trigger] ON [dbo].[AW_RecentRentSale] FOR UPDATE AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 899026484 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_RecentRentSale_dss_tracking] AS [target] 
USING (SELECT [i].[TranId] FROM INSERTED AS [i]) AS source([TranId]) 
ON ([target].[TranId] = [source].[TranId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[TranId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[TranId],@marker_create_scope_local_id, @marker_scope_create_peer_key, @marker_scope_create_peer_timestamp, 0, @marker_local_create_peer_timestamp , NULL, 0, 0, GETDATE() );
GO
CREATE TRIGGER [dbo].[AW_RecentRentSale_dss_insert_trigger] ON [dbo].[AW_RecentRentSale] FOR INSERT AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1 @marker_create_scope_local_id = [provision_scope_local_id], @marker_local_create_peer_timestamp = [provision_timestamp], @marker_local_create_peer_key = [provision_local_peer_key], @marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp], @marker_scope_create_peer_key = [provision_scope_peer_key], @marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 899026484 
 AND [owner_scope_local_id] = 0

MERGE [DataSync].[AW_RecentRentSale_dss_tracking] AS [target] 
USING (SELECT [i].[TranId] FROM INSERTED AS [i]) AS source([TranId]) 
ON ([target].[TranId] = [source].[TranId])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0, 
[local_update_peer_key] = 0, 
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[TranId] ,
[create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp], [local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id], [local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime]) 
VALUES (
[source].[TranId],NULL, NULL, NULL, 0, CAST(@@DBTS AS BIGINT) + 1, NULL, 0, 0, GETDATE() );